// 创建一键报平安数据表的脚本
require('dotenv').config();
const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 数据库配置
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || 'root',
  database: process.env.DB_NAME || 'community_service_platform',
  port: process.env.DB_PORT || 3306
};

async function createSafetyTable() {
  let connection;
  
  try {
    console.log('连接数据库...');
    connection = await mysql.createConnection(dbConfig);
    console.log('数据库连接成功');

    // 读取SQL文件
    const sqlFile = path.join(__dirname, 'safety_reports_table.sql');
    const sqlScript = fs.readFileSync(sqlFile, 'utf8');
    
    // 分割SQL语句（按分号分割）
    const statements = sqlScript
      .split(';')
      .map(stmt => stmt.trim())
      .filter(stmt => stmt.length > 0);

    console.log(`准备执行 ${statements.length} 条SQL语句...`);

    // 执行每条SQL语句
    for (let i = 0; i < statements.length; i++) {
      const statement = statements[i];
      if (statement) {
        try {
          console.log(`执行第 ${i + 1} 条语句...`);
          await connection.execute(statement);
          console.log(`✓ 第 ${i + 1} 条语句执行成功`);
        } catch (error) {
          console.error(`✗ 第 ${i + 1} 条语句执行失败:`, error.message);
          // 如果是表已存在的错误，继续执行
          if (!error.message.includes('already exists')) {
            throw error;
          }
        }
      }
    }

    console.log('\n=== 数据库表创建完成 ===');
    
    // 验证表是否创建成功
    const [tables] = await connection.execute("SHOW TABLES LIKE 'safety_reports'");
    if (tables.length > 0) {
      console.log('✓ safety_reports 表创建成功');
      
      // 检查表结构
      const [columns] = await connection.execute("DESCRIBE safety_reports");
      console.log('\n表结构:');
      console.table(columns);
      
      // 检查现有数据
      const [rows] = await connection.execute("SELECT COUNT(*) as count FROM safety_reports");
      console.log(`\n当前表中有 ${rows[0].count} 条记录`);
    } else {
      console.log('✗ safety_reports 表创建失败');
    }

  } catch (error) {
    console.error('错误:', error.message);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
      console.log('\n数据库连接已关闭');
    }
  }
}

// 运行脚本
if (require.main === module) {
  createSafetyTable();
}

module.exports = createSafetyTable;
